package com.ww.dao.impl;


import com.ww.dao.GoodsDao;
import com.ww.entity.Goods;
import com.ww.util.DBUtil;
import com.ww.util.StringUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 商品表实现类
 */

public class GoodsDaoImpl implements GoodsDao {
    Connection conn = DBUtil.getConnection();
    QueryRunner qr = new QueryRunner();

    @Override
    public int save(Goods obj) {
        String sql = "INSERT INTO  tb_goods(goods_id,goods_name,goods_price,goods_sales,goods_date,goods_type,goods_intro,goods_img) VALUES (?,?,?,?,?,?,?,?)";
        Object[] paras = {obj.getGoods_id(),obj.getGoods_name(),obj.getGoods_price(),obj.getGoods_sales(),obj.getGoods_date()
        ,obj.getGoods_type(),obj.getGoods_intro(),obj.getGoods_img()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Goods obj) {
        String sql = "UPDATE  tb_goods SET  goods_name=?,goods_price=?,goods_sales=?,goods_date=?,goods_type=?,goods_intro=?,goods_img=? WHERE goods_id=? ";
        Object[] paras = {obj.getGoods_name(),obj.getGoods_price(),obj.getGoods_sales(),obj.getGoods_date()
                ,obj.getGoods_type(),obj.getGoods_intro(),obj.getGoods_img(),obj.getGoods_id()};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Object id) {
        String sql = "DELETE FROM tb_goods WHERE goods_id  = ? ";
        Object[] paras = {id};
        try {
            int row = qr.update(conn, sql, paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Goods findById(Object id) {
        String sql = "SELECT * FROM tb_goods where goods_id  = ?";
        Object[] paras = {id};
        try {
            Goods goods = qr.query(conn, sql, paras, new BeanHandler<Goods>(Goods.class));
            return goods;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> findAll() {
        String sql = "SELECT * FROM tb_goods";
        Object[] paras = {};
        try {
            List<Goods> goodsList = qr.query(conn, sql, paras, new BeanListHandler<Goods>(Goods.class));
            return goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> findSearch(String keyword) {
        String sql = "SELECT * FROM tb_goods where goods_name like concat('%',concat(?,'%'))";
        Object[] paras = {keyword};
        try {
            List<Goods> goodsList = qr.query(conn, sql, paras, new BeanListHandler<Goods>(Goods.class));
            return goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> findPage(int page, int pagesize) {
        String sql = "SELECT * FROM tb_goods LIMIT ?,?";
        Object[] paras = {(page-1)*pagesize,pagesize};
        try {
            List<Goods> goodsList = qr.query(conn, sql, paras, new BeanListHandler<Goods>(Goods.class));
            return goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql = "select count(*) from tb_goods";
        Object[] paras = {};
        try {
            long nums = qr.query(conn, sql, paras, new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Goods> findType() {
        String sql = "SELECT * FROM tb_goods GROUP BY goods_type";
        Object[] paras = {};
        try {
            List<Goods> list = qr.query(conn, sql, paras, new BeanListHandler<Goods>(Goods.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> findList(String type) {
        String sql = "SELECT * FROM tb_goods where goods_type = ?";
        Object[] paras = {type};
        try {
            List<Goods> list = qr.query(conn, sql, paras, new BeanListHandler<Goods>(Goods.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Goods findByName(String name) {
        String sql = "SELECT * FROM tb_goods where goods_name = ?";
        Object[] paras = {name};
        try {
            Goods goods = qr.query(conn, sql, paras, new BeanHandler<Goods>(Goods.class));
            return goods;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> findAll(String id) {
        String sql = "SELECT g.goods_id,g.goods_name,g.goods_price,g.goods_date,g.goods_img,u.user_id,u.user_name,t.trolley_num,t.trolley_id FROM tb_trolley t,tb_goods g,tb_user u where t.goods_id = g.goods_id AND t.user_id = u.user_id AND t.user_id = ?";
        Object[] paras = {id};

        try {
            List<Map<String,Object>> mapList = qr.query(conn,sql,paras,new MapListHandler());
            return mapList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
